﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using IsolationLevel = System.Transactions.IsolationLevel;

namespace DMS.Dal.Extensions
{
    public static class DbContextExtension
    {
        internal static void SetIsolationLevel(this DbContext context, IsolationLevel isolationLevel)
        {
            string sql;

            switch (isolationLevel)
            {
                case IsolationLevel.ReadUncommitted:
                    sql = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;";
                    break;
                case IsolationLevel.ReadCommitted:
                    sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;";
                    break;
                default:
                    throw new Exception("ISOLATION LEVEL is not defined in this method.");
            }

            //(context as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(sql, null);
            if (context.Database.Connection.State != ConnectionState.Open)
            {
                // Explicitly open the connection, this connection will close when context is disposed
                context.Database.Connection.Open();
            }

            context.Database.ExecuteSqlCommand(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="values">anonymous class</param>
        /// <returns></returns>
        public static IEnumerable<T> ExecuteProc<T>(this DbContext context, string sql, object values)
        {
            Type t = values.GetType();
            var paramDeclare = new StringBuilder();
            PropertyInfo[] propertyInfos = t.GetProperties();
            var parameters = new SqlParameter[propertyInfos.Length];
            var obj = new object[propertyInfos.Length];
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                object val = propertyInfos[i].GetValue(values);
                paramDeclare.Append("@" + propertyInfos[i].Name + " = {" + i + "},");
                obj[i] = val;
            }

            // param declare @param1 = {0}, @param2 = {1}
            return context.Database.SqlQuery<T>(sql + " " + paramDeclare.ToString().Trim(','), obj);
        }
    }
}